package com.project.common.utils;

import com.project.entity.*;
import com.project.myBeans.ExerciseDetail;
import com.project.myBeans.R;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.commons.io.FileUtils;

import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;


import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * wwh 2018/3/15
 * 对Excel的操作
 */
public class ExcelUtil {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    public static List<User> parse(MultipartFile myFile) throws Exception {
        /**
         * 1.用HSSFWorkbook打开或创建Excel文件对象
         * 2.用HSSFWorkbook对象返回或者创建Sheet对象
         * 3.用Sheet对象返回行对象，用行对象得到Cell对象
         * 4.对Cell对象读写。
         */
        Workbook workbook = null;

        // 获得文件名
        String fileName = myFile.getOriginalFilename();
        if (fileName.endsWith(XLS)) {
            workbook = new HSSFWorkbook(myFile.getInputStream());
        } else if (fileName.endsWith(XLSX)) {
            workbook = new XSSFWorkbook(myFile.getInputStream());
        } else {
            throw new Exception("文件不是Excel文件");
        }
        System.out.println("读取Excel文件正常");

        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();   //指的行数，一共有多少行
        if (rows == 0) {
            throw new Exception("请填写数据");
        }

        List<User> userList = new ArrayList<User>();
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            System.out.println(getCellValue(row.getCell(0)));
            if (row != null) {
                User user = new User();
                user.setUsername(getCellValue(row.getCell(1))); //用户名即学号(工号)
                user.setWorkid(getCellValue(row.getCell(1)));   //学号(工号)
                user.setRealname(getCellValue(row.getCell(2))); //真实姓名
                user.setMobilephone(getCellValue(row.getCell(3)));   //手机号
                userList.add(user);
            }
        }
        return userList;
    }

    /**
     * @author wwh
     * @date 2018/04/14
     * @param multipartFile
     * @param courseid
     * @return
     * @throws Exception
     */
    public static List<ExerciseDetail> parseExercise(MultipartFile multipartFile, int courseid) throws Exception{

        Workbook workbook = null;

        // 获得文件名
        String fileName = multipartFile.getOriginalFilename();
        if (fileName.endsWith(XLS)) {
            workbook = new HSSFWorkbook(multipartFile.getInputStream());
        } else if (fileName.endsWith(XLSX)) {
            workbook = new XSSFWorkbook(multipartFile.getInputStream());
        } else {
            throw new Exception("文件不是Excel文件");
        }
        System.out.println("读取Excel文件正常");

        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();   //指的行数，一共有多少行
//        System.out.println(rows);
        if (rows == 0) {
            throw new Exception("请填写数据");
        }

        List<ExerciseDetail> exerciseDetailList = new ArrayList<>();

        for(int i = 1;i <=rows;i++){    //去除第一行开头
            Row row = sheet.getRow(i);
            if(row!=null){
                ExerciseDetail exerciseDetail = new ExerciseDetail();
                ExerciseInfo exerciseInfo = new ExerciseInfo();
                int type = Integer.parseInt(getCellValue(row.getCell(0)).trim());  //题型
                String content = getCellValue(row.getCell(1));  //题干
                String answers = getCellValue(row.getCell(2));  //正确答案
                String comment = getCellValue(row.getCell(3));  //答案解析
                int dlevel = Integer.parseInt(getCellValue(row.getCell(4)).trim());    //题目难度
                int choicenum = Integer.parseInt(getCellValue(row.getCell(5)).trim()); //选项数
                exerciseInfo.setType(type);
                exerciseInfo.setDlevel(dlevel);
                exerciseInfo.setCourseid(courseid);
                exerciseDetail.setExerciseInfo(exerciseInfo);
                switch (type){
                    case 1:
                    case 2:
                        Choice choice = new Choice();
                        choice.setContent(content);
                        choice.setAnswer(answers);
                        choice.setComment(comment);
                        choice.setLabela(getCellValue(row.getCell(6))); //A选项
                        choice.setLabelb(getCellValue(row.getCell(7))); //B选项
                        choice.setLabelc(getCellValue(row.getCell(8))); //C选项
                        choice.setLabeld(getCellValue(row.getCell(9))); //D选项
                        choice.setLabele(getCellValue(row.getCell(10)));//E选项
                        choice.setLabelf(getCellValue(row.getCell(11)));//F选项
                        exerciseDetail.setChoice(choice);
                        break;
                    case 3:
                        TrueFalse trueFalse = new TrueFalse();
                        trueFalse.setContent(content);
                        trueFalse.setAnswer(Integer.parseInt(answers));
                        trueFalse.setComment(comment);
                        exerciseDetail.setTrueFalse(trueFalse);
                        break;
                    case 4:
                        Completion completion = new Completion();
                        completion.setContent(content);
                        completion.setComment(comment);
                        String[] answerArray = new String[choicenum];
                        for(int j = 0;j < choicenum;j++){
                            answerArray[j] = getCellValue(row.getCell(j+6));
                        }
                        completion.setAnswerArray(answerArray);
                        exerciseDetail.setCompletion(completion);
                        break;
                    case 5:
                        ShortAnswer shortAnswer = new ShortAnswer();
                        shortAnswer.setContent(content);
                        shortAnswer.setComment(comment);
                        shortAnswer.setAnswer(getCellValue(row.getCell(6)));
                        exerciseDetail.setShortAnswer(shortAnswer);
                        break;
                    case 6:
                        Programming programming = new Programming();
                        programming.setContent(content);
                        programming.setComment(comment);
                        programming.setAnswer(getCellValue(row.getCell(6)));
                        exerciseDetail.setProgramming(programming);
                        break;
                }
                exerciseDetailList.add(exerciseDetail);
            }
        }
        return exerciseDetailList;
    }


    /**
     * 获取单元格内容
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            switch (cell.getCellType()) {    // 判断单元格数据类型
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue() + "";
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value;
    }


    /**
     * 导出Excel文件
     * 乱码未解决
     */
//    public static void exportExcel(List<User> userList,HttpServletResponse response) throws Exception{
//        //第一步，创建一个webbook，对应一个Excel文件
////        XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
//        Workbook wb = new XSSFWorkbook();
//        //第二步，在webbook中添加一个sheet，对应Excel文件中的sheet
//        Sheet sheet = wb.createSheet();
//        wb.setSheetName(0,"sheet1");
//        //第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制
////        HSSFRow hssfRow = hssfSheet.createRow(0);
//        Row row = sheet.createRow(0);
//        //第四步，创建单元格，并设置值表头，设置表头居中
////        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
//        CellStyle cellStyle = wb.createCellStyle();
//        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中格式
//
//        Cell cell = row.createCell(0);
//        cell.setCellValue("序号");
//        cell.setCellStyle(cellStyle);
//        cell = row.createCell(1);
//        cell.setCellValue("学号");
//        cell.setCellStyle(cellStyle);
//        cell = row.createCell(2);
//        cell.setCellValue("姓名");
//        cell.setCellStyle(cellStyle);
//        cell = row.createCell(3);
//        cell.setCellValue("手机号");
//        cell.setCellStyle(cellStyle);
//
//        for(int i = 0;i < userList.size();i++){
//            row = sheet.createRow(i+1);
//            User user = userList.get(i);
//            row.createCell(0).setCellValue(i+1);
//            row.createCell(1).setCellValue(user.getUsername());
//            row.createCell(2).setCellValue(user.getRealname());
//            row.createCell(3).setCellValue(user.getMobile());
//        }
//
//        // 第六步，输出Excel文件
//        OutputStream outputStream = response.getOutputStream();
////        FileOutputStream outputStream = new FileOutputStream(new File());
//        response.reset();
//        long filename = System.currentTimeMillis();
//        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
//        String fileName = df.format(new Date());
//        response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
//        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//        wb.write(outputStream);
//        outputStream.close();
//    }


    public static ResponseEntity<byte[]> exportExcel(List<User> userList, HttpServletResponse response) throws Exception {
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wkb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wkb.createSheet("成绩表");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);
        //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("学员考试成绩一览表");
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        //在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班级");
        row2.createCell(2).setCellValue("笔试成绩");
        row2.createCell(3).setCellValue("机试成绩");

//        FileOutputStream out = new FileOutputStream("E://456.xls");
//        wkb.write(out);
//        out.close();

//        ByteArrayOutputStream os = new ByteArrayOutputStream();
//        wkb.write(os);

//        String datetime = DateFormatUtils.format(new Date(),"yyyyMMddHHmmss");
//        String outFile = datetime+".xlsx";
//        response.reset();
//        response.setContentType("application/msexcel;charset=utf-8");
//        response.addHeader("Content-Disposition","attachment;filename="+outFile);
//        response.setContentLength(wkb.getBytes().length);
//        Cookie cookie = new Cookie("fileDownload","true");
//        cookie.setPath("/");
//        ServletOutputStream outputStream = response.getOutputStream();
//        wkb.write(outputStream);
//        wkb.close();
//        outputStream.flush();
//        System.out.println(outputStream.toString());

        File file = new File("E://456.xls");
        byte[] data = FileUtils.readFileToByteArray(file);
        HttpHeaders headers = new HttpHeaders();
        String filename = new String("导入失败数据.xlsx".getBytes("UTF-8"), "iso-8859-1");
        headers.setContentDispositionFormData("attachment", filename);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);//设置MIME类型 以下载方式
        HttpStatus statusCode = HttpStatus.OK;
        return new ResponseEntity<byte[]>(data, headers, statusCode);

    }
}


